In a database system, a trigger is an object that specifies a series of actions to be automatically performed when a specific event occurs. According to industry standards, events that cause triggers to be activated (or "fired") are DML (Data Manipulation Language) statements. Thus, triggers may be designed to fire when a row of a database table is updated, inserted, or deleted. Accordingly, an individual trigger is typically associated with one database table.
The series of actions specified by a trigger is typically written as instructions in a high-level database language such as SQL and PL/SQL, an extension of SQL available from Oracle Corp. of Redwood Shores, Calif. In conformance with industry standards, these instructions must be able to access the data values of table columns corresponding to an affected row before the triggering DML statement was applied (the "old values") and after the modification was applied (the "new values").
Since triggers are objects, database customers can define, remove, and store triggers within a database, and the database system keeps track of which triggers have been defined for which table by storing that information as metadata in the data dictionary. Consequently, triggers enable database customers to implement additional functionality in their databases for such purposes as enforcement of business rules and security.
For example, the owner of a financial database may require for an account table that no account can be opened with a negative balance. In this case, a database administrator defines a trigger that fires when a row is inserted into the account table. The actions specified by the definition of the trigger would direct the database system to check the value of the balance column of the account table, and if the balance is negative, generate an error message. After the database administrator has stored the instructions for the trigger in the database, when a user or application creates a new row in the account table, the database system fires the trigger. When the trigger is fired, the database system loads the trigger instructions as indicated in the metadata for the account table and interprets those instructions. In this example, the instructions check the opening account balance and generate an error message when the balance is negative.
Another use for triggers is data replication. Under certain conditions, it is desirable to store copies of a particular body of data, such as a table in a relational database, at multiple sites. If users are allowed to update the body of data at one site, the updates must be propagated to copies at other sites in order for the copies to remain consistent. The process of propagating changes is generally referred to as replication.
Some databases implement replication by defining triggers when DML statements are executed, for example, when a user inserts, modifies, or deletes a row in a table. In response to such a firing event, a replication trigger performs a series of operations that ultimately results in the change being propagating to the replication sites. For example, a replication trigger may submit the modification to a job queue. A replicator process periodically inspects the job queue for replications to perform, and upon finding jobs in the queue, performs those replications. In another example, a replication trigger can propagate changes to another site before the current transaction commits. In either example, the performance of the replication trigger impacts the performance of the user statement which inserted, modified, or deleted the row.
The trigger definitions for replication can be created by the database system itself from parameters supplied by a database administrator. These trigger-type specific parameters define details about the replication, such as what kind of replication is requested, which table is to be replicated, and which columns of the table are to be replicated. Thus, a database administrator only has to submit the particular parameters of a replication through a user interface, and the database system itself generates the requisite trigger definitions from the parameters.
Replication is one environment in which a single operation from a user can cause the same kind of DML statement to be applied and, hence, the same trigger to fire, repeatedly for a large number of rows. In such environments, the disadvantages of conventional trigger implementations are especially evident. In specific, conventional trigger implementations suffer in the areas of performance and memory usage.
Conventionally, trigger instructions are written in a high-level language, which must be interpreted at a substantial performance cost. Accordingly, some database systems provide a way for partially compiling or tokenizing those trigger instructions into "stored procedures." The trigger's processing environment, such as the run-time memory configuration, has to be initialized each time a conventional trigger is fired. For example, replication triggers employ a replication-specific parameter that specifies the site to which the changes are to be propagated. This parameter must be loaded into a configured section of memory each time the trigger is fired, although the parameter has a constant value for that trigger. As an another example, the trigger instructions may need to have access to such DML statement metadata as the name of the table for which the trigger fired.
Some database systems attempt to reduce the performance penalty of trigger parameters by hard-coding them into the trigger instructions as constants and literals. However, this approach results in trigger proliferation. In other words, since parameters are hard-coded, a trigger for each table must use a distinct sequence of instructions. Each trigger consumes much memory. If, for example, there are thousands of replicated tables, then there are thousands of triggers. Accordingly, trigger proliferation consumes much memory. Furthermore, proliferated triggers with hard-coded parameters cannot reuse the same run-time memory configuration, initialized by a similar trigger. Therefore, it is desirable to reduce the memory consumption caused by trigger proliferation.
Another drawback to conventional triggers is that their instructions are stored in the database and are consequently open to intentional or accidental modification by users of the database system. If a user modifies a replication trigger, the database system can no longer guarantee correct performance of the data replication. The modified trigger instructions may contain errors that prevent the replication or even corrupt other data in the database. Thus, there is a need for secure triggers.